package com.gcxy.utils;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.baomidou.mybatisplus.core.toolkit.ObjectUtils;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.gcxy.core.R;
import com.gcxy.dto.evaluate.EvaluateExcelDTO;
import com.gcxy.dto.project.ProjectFileDTO;
import com.gcxy.dto.weekly.WeeklyEvaluateDTO;
import com.gcxy.enums.ResultCodeEnum;
import com.gcxy.vo.student.StudentPageVO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.compress.utils.IOUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.List;

import static cn.hutool.core.util.ClassUtil.getClassLoader;

/**
 * Copyright (C), 2024-2024, BUG闭退！
 * Author: Huang先森
 * Date: 2024/7/29 11:44
 * FileName: ExcelUtil
 * Description:
 */
@Slf4j
public class ExcelUtil {
    public static void writeExcel(HttpServletResponse response, String fileName, String sheetName, Class clazz, List<? extends Object> data,List<Integer> fieldsToExclude) throws Exception {
        //表头样式
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        //设置表头居中对齐
        headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        //内容样式
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        //设置内容靠左对齐
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
        HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        EasyExcel.write(getOutputStream(fileName, response), clazz)
                .excelType(ExcelTypeEnum.XLSX)
                .sheet(sheetName)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .excludeColumnIndexes(fieldsToExclude)
//                .excludeColumnFiledNames(fieldsToExclude) // 排除不需要的字段
                .doWrite(data);
    }

    private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
        fileName = URLEncoder.encode(fileName, "UTF-8");
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf8");
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
        return response.getOutputStream();
    }

    /**
     * 校验
     * @param file
     * @return
     */
    public static R<?> check(MultipartFile file) {
        if (file == null || file.isEmpty() || ObjectUtils.isEmpty(file.getOriginalFilename())) {
            return R.Failed(ResultCodeEnum.BUSINESS_ERROR.getCode(), "文件不能为空！");
        }
        String fileName = file.getOriginalFilename();
        if (!(fileName.endsWith(".xlsx") || fileName.endsWith(".xls"))) {
            return R.Failed(ResultCodeEnum.BUSINESS_ERROR.getCode(), "文件类型错误，只支持：xlsx、xls！");
        }
        return R.Success("文件符合要求");
    }


    /**
     * excel表中填充数据
     *
     * @param workBook
     * @param weeklyEvaluateDto
     */
    public static void fillData(XSSFWorkbook workBook, WeeklyEvaluateDTO weeklyEvaluateDto) {
        //获取第一个sheet页
        XSSFSheet sheet = workBook.getSheetAt(0);
        //设置单元格数据类型
        XSSFCellStyle cellStyle = setCommonCellStyle(workBook);
        //设置第二行数据
        XSSFRow row2 = sheet.createRow(1);
        XSSFCell cell2 = row2.createCell(0);
        StringBuilder data = new StringBuilder("学期：").append(weeklyEvaluateDto.getSemesterName()).append("     ")
                .append("学生姓名：").append(weeklyEvaluateDto.getStudentName()).append("     ")
                .append("学号：").append(weeklyEvaluateDto.getStudentAccount());
        cell2.setCellValue(data.toString());
        cell2.setCellStyle(setCell2Style(workBook));
        //设置其他数据
        XSSFRow row = null;
        Cell cell = null;
        //循环填充数据
        for (int i = 0; i < weeklyEvaluateDto.getEvaluateExcelDtoList().size(); i++) {
            //从第六行开始填充
            row = sheet.createRow(i + 5);
            EvaluateExcelDTO evaluateExcelDto = weeklyEvaluateDto.getEvaluateExcelDtoList().get(i);
            //填充11列的数据
            for (int j = 0; j < 11; j++) {
                if (j == 10) {
                    if (StringUtils.isBlank(evaluateExcelDto.getData(j))){
                        continue;
                    }
                    //第10行插入教师电子签名
                    try {
                        setSignature(workBook, sheet, evaluateExcelDto.getData(j), j, i + 5);
                    } catch (Exception e) {
                        log.error("插入教师电子签名错误：{}", e.getMessage());
                    }
                } else {
                    //你有11列那就赋值下去咯，这里不是统一的，看你的数据格式
                    //这里是第i行创建第x列
                    cell = row.createCell(j);
                    //使用的是上面我们创建的正文格式
                    cell.setCellStyle(cellStyle);
                    //给这个列表赋值，但是一定要是string不然会报错
                    cell.setCellValue(evaluateExcelDto.getData(j));
                }
            }
        }
    }

    /**
     * 设置公共单元格风格类型
     *
     * @param workBook
     * @return
     */
    private static XSSFCellStyle setCommonCellStyle(XSSFWorkbook workBook) {
        XSSFCellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //设置对齐类型
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐类型
        //设置单元格边框
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        cellStyle.setWrapText(true); //这是是否换行文本
        //设置字体
        XSSFFont font = workBook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);//设置字体大小
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 设置第二行单元格风格类型
     *
     * @param workBook
     * @return
     */
    private static XSSFCellStyle setCell2Style(XSSFWorkbook workBook) {
        XSSFCellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //设置对齐类型
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐类型
        //设置单元格边框
        cellStyle.setBorderBottom(BorderStyle.NONE); //下边框
        cellStyle.setBorderLeft(BorderStyle.NONE); //左边框
        cellStyle.setBorderRight(BorderStyle.NONE); //右边框
        cellStyle.setBorderTop(BorderStyle.NONE); //上边框
        cellStyle.setWrapText(true); //这是是否换行文本
        //设置字体
        XSSFFont font = workBook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 10);//设置字体大小
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 设置项目学生清单单元格样式
     *
     * @param workBook
     * @return
     */
    private static XSSFCellStyle setProjectCellStyleNoBorder(XSSFWorkbook workBook, int fontSize) {
        XSSFCellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //设置对齐类型
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐类型
        //设置单元格边框
        cellStyle.setBorderBottom(BorderStyle.NONE); //下边框
        cellStyle.setBorderLeft(BorderStyle.NONE); //左边框
        cellStyle.setBorderRight(BorderStyle.NONE); //右边框
        cellStyle.setBorderTop(BorderStyle.NONE); //上边框
        cellStyle.setWrapText(true); //这是是否换行文本
        //设置字体
        XSSFFont font = workBook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) fontSize);//设置字体大小
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * 设置公共单元格风格类型
     *
     * @param workBook
     * @return
     */
    private static XSSFCellStyle setProjectCellStyle(XSSFWorkbook workBook) {
        XSSFCellStyle cellStyle = workBook.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER); //设置对齐类型
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐类型
        //设置单元格边框
        cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle.setBorderLeft(BorderStyle.THIN); //左边框
        cellStyle.setBorderRight(BorderStyle.THIN); //右边框
        cellStyle.setBorderTop(BorderStyle.THIN); //上边框
        cellStyle.setWrapText(true); //这是是否换行文本
        //设置字体
        XSSFFont font = workBook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);//设置字体大小
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     * @param workBook
     * @param sheet             sheet页（默认第一页）
     * @param signatureFilePath 电子签名路径
     * @param colSize           excel图片开始列
     * @param rowSize           excel图片开始行
     * @throws IOException
     */
    private static void setSignature(XSSFWorkbook workBook, XSSFSheet sheet, String signatureFilePath, int colSize, int rowSize) throws IOException {
        // 读取图片文件
        FileInputStream fis = new FileInputStream(signatureFilePath);
        // 将图片数据转换为字节数组
        byte[] imageBytes = IOUtils.toByteArray(fis);
        fis.close();
        // 将图片数据插入到工作表中
        int pictureIdx = workBook.addPicture(imageBytes, Workbook.PICTURE_TYPE_JPEG);
        // 创建绘图对象
        CreationHelper helper = workBook.getCreationHelper();
        //sheet创建电子表格ml绘图00000
        Drawing drawing = sheet.createDrawingPatriarch();
        // 创建锚点，设置图片位置和大小
        ClientAnchor anchor = helper.createClientAnchor();
        anchor.setCol1(colSize); // 图片开始列
        anchor.setRow1(rowSize); // 图片开始行
        //填充图片
        Picture picture = drawing.createPicture(anchor, pictureIdx);
        // 设置图片大小
        picture.resize(0.8, 0.8);
    }

    /**
     * 生成学生周评价excel文件
     */
    public static void createExcel(String targetPath, WeeklyEvaluateDTO weeklyEvaluateDto) {
        try {
            String folderName = targetPath.substring(0, targetPath.lastIndexOf("/"));
            File file = new File(folderName);
            if (!file.exists()) {
                file.mkdirs();
            }
            InputStream inputStream = getClassLoader().getResourceAsStream("学生周评价表.xlsx");
            XSSFWorkbook workBook = new XSSFWorkbook(inputStream);
            ExcelUtil.fillData(workBook, weeklyEvaluateDto);
            workBook.write(Files.newOutputStream(Paths.get(targetPath)));
            workBook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void createProjectStudentListExcel(String filePath, List<ProjectFileDTO> projectFileDTOList, List<StudentPageVO> studentPageVOList) {
        try {
            String folderName = filePath.substring(0, filePath.lastIndexOf("/"));
            File file = new File(folderName);
            if (!file.exists()) {
                file.mkdirs();
            }
            //2.1读取模板
            InputStream inputStream = getClassLoader().getResourceAsStream("软件学院商业项目实作指导教师学生清单.xlsx");
            XSSFWorkbook workBook = new XSSFWorkbook(inputStream);
            //2.2填充数据
            ExcelUtil.fillProjectExcelData(workBook, projectFileDTOList, studentPageVOList);
            workBook.write(Files.newOutputStream(Paths.get(filePath)));
            workBook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static void fillProjectExcelData(XSSFWorkbook workBook, List<ProjectFileDTO> projectFileDTOList, List<StudentPageVO> studentPageVOList) {
        //设置项目清单表
        if (CollectionUtils.isNotEmpty(projectFileDTOList)){
            for (int i = 0; i < projectFileDTOList.size(); i++) {
                ProjectFileDTO projectFileDTO = projectFileDTOList.get(i);
                String projectName = projectFileDTO.getProjectName();
                String semesterName = projectFileDTO.getSemesterName();
                //拿模板sheet
                XSSFSheet sheetAt = workBook.getSheetAt(0);
                //根据第一个Sheet创建一个新的Sheet
                XSSFSheet sheet = workBook.cloneSheet(0, projectName);
                //复制表格中倒数第二行
                XSSFRow lastRow31 = sheet.getRow(31);
                XSSFRow lastRow32 = sheet.getRow(32);
                int lastRow2Num = projectFileDTO.getStudentPageVOList().size() + 3;
                int lastRow1Num = projectFileDTO.getStudentPageVOList().size() + 4;
                if (projectFileDTO.getStudentPageVOList().size() > 28){
                    //删除32行和33行
                    sheet.removeRow(lastRow31);
                    unmergeCell(sheet, 31, 0);
                    sheet.removeRow(lastRow32);
                    unmergeCell(sheet, 32, 0);
                    //把sheetAt的31行对象复制给lastRow2对象
                    copyRow(workBook,sheetAt, 31, sheet, lastRow2Num);
                    sheet.addMergedRegion(new CellRangeAddress(lastRow2Num, lastRow2Num, 0, 7));
                    copyRow(workBook,sheetAt, 32, sheet, lastRow1Num);
                    sheet.addMergedRegion(new CellRangeAddress(lastRow1Num, lastRow1Num, 0, 7));
                }
                //设置第二行数据
                XSSFRow row2 = sheet.createRow(1);
                //设置行高
                row2.setHeightInPoints(28);
                //设置第2格
                XSSFCell cell2 = row2.createCell(1);
                StringBuilder cell2Data = new StringBuilder("项目组：").append(projectName);
                cell2.setCellValue(cell2Data.toString());
                cell2.setCellStyle(setProjectCellStyleNoBorder(workBook,11));
                //设置第5格
                XSSFCell cell5 = row2.createCell(5);
                StringBuilder cell5Data = new StringBuilder("学期：").append(semesterName);
                cell5.setCellValue(cell5Data.toString());
                cell5.setCellStyle(setProjectCellStyleNoBorder(workBook,11));
                //设置其他数据
                //循环填充数据
                for (int j = 0; j < projectFileDTO.getStudentPageVOList().size(); j++) {
                    StudentPageVO studentPageVO = projectFileDTO.getStudentPageVOList().get(j);
                    //设置行高
                    XSSFRow row = sheet.createRow(j + 3);
                    row.setHeightInPoints(28);
                    //如果j大于27则是插入行
                    //设置第1格
                    XSSFCell studentCell1 = row.createCell(0);
                    studentCell1.setCellValue(j+1);
                    studentCell1.setCellStyle(setProjectCellStyle(workBook));
                    //设置第2格
                    XSSFCell studentCell2 = row.createCell(1);
                    studentCell2.setCellValue(studentPageVO.getPositionName());
                    studentCell2.setCellStyle(setProjectCellStyle(workBook));
                    //设置第3格
                    XSSFCell studentCell3 = row.createCell(2);
                    studentCell3.setCellValue(studentPageVO.getHonoraryTeacherName());
                    studentCell3.setCellStyle(setProjectCellStyle(workBook));
                    //设置第4格
                    XSSFCell studentCell4 = row.createCell(3);
                    studentCell4.setCellValue("学院导师");
                    studentCell4.setCellStyle(setProjectCellStyle(workBook));
                    //设置第5格
                    XSSFCell studentCell5 = row.createCell(4);
                    studentCell5.setCellStyle(setProjectCellStyle(workBook));
                    //设置第6格
                    XSSFCell studentCell6 = row.createCell(5);
                    studentCell6.setCellStyle(setProjectCellStyle(workBook));
                    //设置第7格
                    XSSFCell studentCell7 = row.createCell(6);
                    studentCell7.setCellValue(studentPageVO.getStudentName());
                    studentCell7.setCellStyle(setProjectCellStyle(workBook));
                    //设置第8格
                    XSSFCell studentCell8 = row.createCell(7);
                    studentCell8.setCellValue(studentPageVO.getStudentAccount());
                    studentCell8.setCellStyle(setProjectCellStyle(workBook));
                }

//                //创建倒数第二行
//                XSSFRow lastRow2 = sheet.createRow(projectFileDTO.getStudentPageVOList().size() + 3);
//                lastRow2.setHeightInPoints(34);
//                //创建倒数第二行的第一格并让其跨5列
//                XSSFCell lastCell2 = lastRow2.createCell(0);
//                lastCell2.setCellValue("              制表人：                制表日期：               研究所负责人签名：\t\t\t\t\t\t\t\n");
//                lastCell2.setCellStyle(setProjectCellStyleNoBorder(workBook,11));
//                //创建导数第三行
//                XSSFRow lastRow1 = sheet.createRow(projectFileDTO.getStudentPageVOList().size() + 4);
//                lastRow1.setHeightInPoints(34);
//                XSSFCell lastCell1 = lastRow2.createCell(0);
//                lastCell1.setCellValue("注：原则上1位指导教师指导不超过5位学生，指导教师指导的学生名单及岗位发生变化，需及时更新此表并报对应系部备案\t\t\t\t\t\t\t\n");
//                lastCell1.setCellStyle(setProjectCellStyleNoBorder(workBook,10));
            }
        }
        //设置总表
        if (CollectionUtils.isNotEmpty(studentPageVOList)){
            //获取总表
            XSSFSheet sheetSum = workBook.getSheetAt(1);
            //循环填充数据
            for (int j = 0; j < studentPageVOList.size(); j++) {
                StudentPageVO studentPageVO = studentPageVOList.get(j);
                //设置行高
                XSSFRow row = sheetSum.createRow(j + 1);
                row.setHeightInPoints(15);
                //如果j大于27则是插入行
                //设置第1格
                XSSFCell studentCell1 = row.createCell(0);
                studentCell1.setCellValue(studentPageVO.getStudentAccount());
                studentCell1.setCellStyle(setProjectCellStyle(workBook));
                //设置第2格
                XSSFCell studentCell2 = row.createCell(1);
                studentCell2.setCellValue(studentPageVO.getStudentName());
                studentCell2.setCellStyle(setProjectCellStyle(workBook));
                //设置第3格
                XSSFCell studentCell3 = row.createCell(2);
                studentCell3.setCellValue(studentPageVO.getPositionName());
                studentCell3.setCellStyle(setProjectCellStyle(workBook));
                //设置第4格
                XSSFCell studentCell4 = row.createCell(3);
                studentCell4.setCellValue(String.join(",",studentPageVO.getProjectList()));
                studentCell4.setCellStyle(setProjectCellStyle(workBook));
                //设置第5格
                XSSFCell studentCell5 = row.createCell(4);
                studentCell5.setCellValue(studentPageVO.getClassName());
                studentCell5.setCellStyle(setProjectCellStyle(workBook));
                //设置第6格
                XSSFCell studentCell6 = row.createCell(5);
                studentCell6.setCellValue(studentPageVO.getHonoraryTeacherUsername());
                studentCell6.setCellStyle(setProjectCellStyle(workBook));
                //设置第7格
                XSSFCell studentCell7 = row.createCell(6);
                studentCell7.setCellValue(studentPageVO.getHonoraryTeacherName());
                studentCell7.setCellStyle(setProjectCellStyle(workBook));
            }
        }
        //删除模板表
        workBook.removeSheetAt(0);
    }

    private static void unmergeCell(XSSFSheet sheet, int rowIndex, int columnIndex) {
        // 获取合并区域列表
        int numMergedRegions = sheet.getNumMergedRegions();

        for (int i = 0; i < numMergedRegions; i++) {
            CellRangeAddress region = sheet.getMergedRegion(i);
            if (region.isInRange(rowIndex, columnIndex)) {
                // 如果找到了包含目标单元格的合并区域，则取消合并
                sheet.removeMergedRegion(i);
                break;
            }
        }
    }

    private static void copyRow(XSSFWorkbook workBook,XSSFSheet sourceSheet, int sourceRowIndex, XSSFSheet targetSheet, int targetRowIndex) {
        XSSFRow sourceRow = sourceSheet.getRow(sourceRowIndex);
        if (sourceRow == null) {
            throw new IllegalArgumentException("Source row at index " + sourceRowIndex + " does not exist.");
        }

        // 如果目标行不存在，则创建新行
        XSSFRow targetRow = targetSheet.getRow(targetRowIndex);
        if (targetRow == null) {
            targetRow = targetSheet.createRow(targetRowIndex);
        }
        targetRow.setHeightInPoints(sourceRow.getHeightInPoints());

        // 复制单元格
        for (int c = 0; c < sourceRow.getLastCellNum(); c++) {
            XSSFCell sourceCell = sourceRow.getCell(c);
            if (sourceCell == null) {
                continue;
            }

            XSSFCell targetCell = targetRow.createCell(c);
            targetCell.setCellValue(sourceCell.getStringCellValue());
            targetCell.setCellStyle(sourceCell.getCellStyle());
        }
    }
}
